﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.Odbc;

using ConversaoSIC.BRL;

namespace ConversaoSIC
{

    public partial class frmQuery : Form
    {


        public frmQuery()
        {
            InitializeComponent();
        }

        private void btnExecutar_Click(object sender, EventArgs e)
        {
            //try
            //{
            //    OleDbConnection cn = new OleDbConnection(cnx);
            //    OleDbCommand cmd = cn.CreateCommand();
            //    cn.Open();
            //    cmd.CommandText = "SELECT * FROM TABEST1";
            //    cmd.CommandType = CommandType.Text;
            //    cmd.ExecuteNonQuery();

            //    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM TABEST1", cn);
            //    DataSet dsRetrievedData = new DataSet();
            //    da.Fill(dsRetrievedData);
            //    this.dataGridView1.DataSource = dsRetrievedData;
            //    this.dataGridView1.DataMember = dsRetrievedData.Tables[0].TableName;
            //    cn.Close();
            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message);
            //}
            /************************************/
            try
            {
                OleDbDataAdapter OleAdapt = ConnectParadox(@"C:\Projeto Arabian\Dados", txtTabela.Text);
                DataTable oleTable = new DataTable("tablename");
                OleAdapt.Fill(oleTable);
                dataGridView1.DataSource = oleTable;
                //this.Text = dataGridView1.RowCount.ToString(); //this returns 7811, but Count thing returns 7810
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            
            
            //try
            //{
            //    DataSet ds = new ConversaoBRL().LerDados(txtTabela.Text);
            //    dataGridView1.DataSource = ds;
            //    dataGridView1.DataMember = ds.Tables[0].TableName;
            //    //new ConversaoBRL().CarregaBanco();
            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.ToString());
            //}
        }



        private OleDbDataAdapter ConnectParadox(string pPath, string pQuery)
        {
            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + pPath + "; Extended Properties=Paradox 5.x";
            try
            {
                OleDbConnection oleConnection = new OleDbConnection(strConnection);
                oleConnection.Open();
                OleDbDataAdapter oleAdapter = new OleDbDataAdapter(pQuery, oleConnection);
                return oleAdapter;
            }
            catch (OleDbException e)
            {
                MessageBox.Show(e.Message);
                return null;
            }
        }

        private void txtTabela_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar.Equals((char)Keys.Enter))
            {
                btnExecutar_Click(null, null);
            }
        }

        private void lerExcel()
        {
            //Create the Connection String
            //You must specify the Provider as is here, I think
            //Thre Data Source is the path to your file
            //Extended Properties is something to do with excel, use as here

            string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
					Data Source=C:\produto.xls;
					Extended Properties=Excel 5.0";

            //Create the connection
            OleDbConnection ExcelConnection = new OleDbConnection(ConnectionString);

            //create a string for the query
            string ExcelQuery;

            //Sheet1 is the sheet name
            //create the query:
            //read column with heading A from the Excel file

            ExcelQuery = "Select * from [produto$]"; // from Sheet1";

            //use "Select * ... " to select the entire sheet
            //create the command

            OleDbCommand ExcelCommand = new OleDbCommand(ExcelQuery, ExcelConnection);
            //Open the connection

            ExcelConnection.Open();

            //Create a reader

            OleDbDataReader ExcelReader;
            ExcelReader = ExcelCommand.ExecuteReader();

            //For each row after the first
            //Message box the values in the first column i.e. column 0

            while (ExcelReader.Read())
            {
                MessageBox.Show((ExcelReader.GetValue(0)).ToString() +
                ExcelReader.GetValue(1).ToString() +
                Decimal.Parse(ExcelReader.GetValue(2).ToString()));
            }
            ExcelConnection.Close();

            //Try update the file

            ExcelQuery = "Update [Plan1$] set B = 2 where B = 1"; // from Sheet1";

            //Create the command to be executed

            ExcelCommand = new OleDbCommand(ExcelQuery, ExcelConnection);

            //Open the connection to the file

            ExcelConnection.Open();

            //Execute the update

            ExcelCommand.ExecuteNonQuery();

            //Close the connection

            ExcelConnection.Close();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            new frmMaster().Show();
        }
        private void frmQuery_Load(object sender, EventArgs e)
        {
            //Clock = new Timer();
            //Clock.Interval = 1000;
            //Clock.Start();
            //Clock.Tick += new EventHandler(Timer_Tick);
            timer1.Enabled = true;
         }

        //public void Timer_Tick(object sender, EventArgs eArgs)
        //{
        //    if (sender == Clock)
        //    {
        //        lblTempo.Text = DateTime.Now.ToString();
        //        lblTempo.Refresh();
        //    }
        //}

        private void timer1_Tick(object sender, EventArgs e)
        {
            lblTempo.Text = DateTime.Now.ToString();
            lblTempo.Refresh();
        }

    }
}